SET  ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, QUOTED_IDENTIFIER ON 
SET  NUMERIC_ROUNDABORT OFF
GO
:setvar DatabaseName "MDDE"
:setvar PrimaryFilePhysicalName "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MDDE.mdf"
:setvar PrimaryLogFilePhysicalName "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MDDE_log.ldf"

USE [master]

GO

:on error exit

IF  (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END
GO

:on error exit

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END
GO
CREATE DATABASE [$(DatabaseName)] ON ( NAME = N'PrimaryFileName', FILENAME = N'$(PrimaryFilePhysicalName)') LOG ON ( NAME = N'PrimaryLogFileName', FILENAME = N'$(PrimaryLogFilePhysicalName)') COLLATE SQL_Latin1_General_CP1_CI_AS 

GO

:on error resume
     
EXEC sp_dbcmptlevel N'$(DatabaseName)', 90

GO

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	ALLOW_SNAPSHOT_ISOLATION OFF
GO

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	READ_COMMITTED_SNAPSHOT OFF
GO

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	MULTI_USER,
	CURSOR_CLOSE_ON_COMMIT OFF,
	CURSOR_DEFAULT LOCAL,
	AUTO_CLOSE OFF,
	AUTO_CREATE_STATISTICS ON,
	AUTO_SHRINK OFF,
	AUTO_UPDATE_STATISTICS ON,
	AUTO_UPDATE_STATISTICS_ASYNC ON,
	ANSI_NULL_DEFAULT ON,
	ANSI_NULLS ON,
	ANSI_PADDING ON,
	ANSI_WARNINGS ON,
	ARITHABORT ON,
	CONCAT_NULL_YIELDS_NULL ON,
	NUMERIC_ROUNDABORT OFF,
	QUOTED_IDENTIFIER ON,
	RECURSIVE_TRIGGERS OFF,
	RECOVERY FULL,
	PAGE_VERIFY NONE,
	DISABLE_BROKER,
	PARAMETERIZATION SIMPLE
	WITH ROLLBACK IMMEDIATE
GO

IF IS_SRVROLEMEMBER ('sysadmin') = 1
BEGIN

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    EXEC sp_executesql N'
    ALTER DATABASE [$(DatabaseName)] SET  
	DB_CHAINING OFF,
	TRUSTWORTHY OFF'

END
ELSE
BEGIN
    RAISERROR(N'Unable to modify the database settings for DB_CHAINING or TRUSTWORTHY. You must be a SysAdmin in order to apply these settings.',0,1)
END

GO

USE [$(DatabaseName)]

GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script	
 Use SQLCMD syntax to include a file into the pre-deployment script			
 Example:      :r .\filename.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/








GO

:on error resume
GO
PRINT N'Creating [dbo].[InstanceColumnProperties]'
GO
CREATE TABLE [dbo].[InstanceColumnProperties]
(
[ColumnId] [int] NOT NULL,
[PropertyName] [nvarchar] (256) NOT NULL,
[PropertyValue] [nvarchar] (4000) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_InstanceColumnProperties] on [dbo].[InstanceColumnProperties]'
GO
ALTER TABLE [dbo].[InstanceColumnProperties] ADD CONSTRAINT [PK_InstanceColumnProperties] PRIMARY KEY CLUSTERED  ([ColumnId], [PropertyName]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_DeleteInstanceColumnProperty]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteInstanceColumnProperty] 
    @columnId		int,
	@propertyName	nvarchar(256)
AS 
BEGIN

DELETE FROM InstanceColumnProperties WHERE ColumnId = @columnId AND PropertyName = @propertyName;

END
GO
PRINT N'Creating [dbo].[TemplateInstances]'
GO
CREATE TABLE [dbo].[TemplateInstances]
(
[InstanceId] [int] NOT NULL IDENTITY(1, 1),
[TemplateId] [int] NULL,
[TemplateInstanceName] [nvarchar] (256) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK__TemplateInstance__1ED998B2] on [dbo].[TemplateInstances]'
GO
ALTER TABLE [dbo].[TemplateInstances] ADD CONSTRAINT [PK__TemplateInstance__1ED998B2] PRIMARY KEY CLUSTERED  ([InstanceId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[TemplateInstanceComponents]'
GO
CREATE TABLE [dbo].[TemplateInstanceComponents]
(
[ComponentId] [int] NOT NULL IDENTITY(1, 1),
[ComponentClassId] [nvarchar] (128) NOT NULL,
[ComponentName] [nvarchar] (256) NOT NULL,
[TemplateInstanceId] [int] NOT NULL,
[ComponentUri] [nvarchar] (2048) NOT NULL,
[ComponentType] [nvarchar] (64) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_TemplateInstanceComponents] on [dbo].[TemplateInstanceComponents]'
GO
ALTER TABLE [dbo].[TemplateInstanceComponents] ADD CONSTRAINT [PK_TemplateInstanceComponents] PRIMARY KEY CLUSTERED  ([ComponentId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_DeleteInstanceComponent]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteInstanceComponent] 
    @componentId int
AS 
BEGIN

-- Delete the component.
DELETE FROM TemplateInstanceComponents WHERE ComponentId = @componentId;

END
GO
PRINT N'Creating [dbo].[Packages]'
GO
CREATE TABLE [dbo].[Packages]
(
[PackageName] [varchar] (256) NOT NULL,
[PackageVersion] [varchar] (20) NOT NULL,
[TemplateId] [int] NOT NULL,
[PackageBody] [xml] NOT NULL,
[ChangesFromTemplate] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_Packages_1] on [dbo].[Packages]'
GO
ALTER TABLE [dbo].[Packages] ADD CONSTRAINT [PK_Packages_1] PRIMARY KEY CLUSTERED  ([PackageName], [TemplateId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_CreatePackage]'
GO
CREATE PROCEDURE [dbo].[sp_CreatePackage] 
    @packageName varchar(256), 
	@packageVersion varchar(64),
    @templateID int,
	@packageBody xml,
	@changesFromTemplate xml
AS 
BEGIN

INSERT INTO Packages(PackageName,PackageVersion,TemplateID,PackageBody, ChangesFromTemplate) VALUES(@packageName,@packageVersion,@templateID,@packageBody, @changesFromTemplate);

END
GO
PRINT N'Creating [dbo].[InstanceColumnCollections]'
GO
CREATE TABLE [dbo].[InstanceColumnCollections]
(
[CollectionId] [int] NOT NULL IDENTITY(1, 1),
[ComponentId] [int] NOT NULL,
[CollectionType] [nvarchar] (128) NOT NULL,
[CollectionName] [nvarchar] (256) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_InstanceColumnCollections] on [dbo].[InstanceColumnCollections]'
GO
ALTER TABLE [dbo].[InstanceColumnCollections] ADD CONSTRAINT [PK_InstanceColumnCollections] PRIMARY KEY CLUSTERED  ([CollectionId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[InstanceColumns]'
GO
CREATE TABLE [dbo].[InstanceColumns]
(
[ColumnId] [int] NOT NULL IDENTITY(1, 1),
[CollectionId] [int] NOT NULL,
[ColumnName] [nvarchar] (256) NOT NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_InstanceColumns] on [dbo].[InstanceColumns]'
GO
ALTER TABLE [dbo].[InstanceColumns] ADD CONSTRAINT [PK_InstanceColumns] PRIMARY KEY CLUSTERED  ([ColumnId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[InstanceScalarProperties]'
GO
CREATE TABLE [dbo].[InstanceScalarProperties]
(
[PropertyName] [nvarchar] (256) NOT NULL,
[PropertyValue] [nvarchar] (4000) NULL,
[PropertyType] [nvarchar] (128) NULL,
[ComponentId] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_InstanceScalarProperties] on [dbo].[InstanceScalarProperties]'
GO
ALTER TABLE [dbo].[InstanceScalarProperties] ADD CONSTRAINT [PK_InstanceScalarProperties] PRIMARY KEY CLUSTERED  ([PropertyName], [ComponentId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_DeleteInstanceScalarProperty]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteInstanceScalarProperty] 
	@componentId		int,
	@propertyName		nvarchar(256)
AS 
BEGIN

DELETE FROM InstanceScalarProperties WHERE ComponentId = @componentId AND PropertyName = @propertyName;

END
GO
PRINT N'Creating [dbo].[InstanceColumnCollectionProperties]'
GO
CREATE TABLE [dbo].[InstanceColumnCollectionProperties]
(
[CollectionId] [int] NOT NULL,
[PropertyName] [nvarchar] (256) NOT NULL,
[PropertyValue] [nvarchar] (4000) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_InstanceColumnCollectionProperties] on [dbo].[InstanceColumnCollectionProperties]'
GO
ALTER TABLE [dbo].[InstanceColumnCollectionProperties] ADD CONSTRAINT [PK_InstanceColumnCollectionProperties] PRIMARY KEY CLUSTERED  ([CollectionId], [PropertyName]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[TemplateScalarProperties]'
GO
CREATE TABLE [dbo].[TemplateScalarProperties]
(
[ComponentId] [int] NOT NULL,
[PropertyName] [nvarchar] (256) NOT NULL,
[PropertyType] [nvarchar] (128) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_TemplateScalarProperties_1] on [dbo].[TemplateScalarProperties]'
GO
ALTER TABLE [dbo].[TemplateScalarProperties] ADD CONSTRAINT [PK_TemplateScalarProperties_1] PRIMARY KEY CLUSTERED  ([ComponentId], [PropertyName]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_CreateTemplateScalarProperty]'
GO
CREATE PROCEDURE [dbo].[sp_CreateTemplateScalarProperty] 
    @componentId		bigint, 
	@propertyName		nvarchar(256), 
	@propertyType		nvarchar(128)
AS 
BEGIN
	INSERT INTO TemplateScalarProperties
	(ComponentId, PropertyName, PropertyType)
	VALUES (
		@componentId, 
		@propertyName, 
		@propertyType);

END
GO
PRINT N'Creating [dbo].[sp_CreateInstanceColumn]'
GO
CREATE PROCEDURE [dbo].[sp_CreateInstanceColumn] 
    @collectionId int, 
	@columnName nvarchar(256)
AS 
BEGIN
INSERT INTO InstanceColumns(CollectionId,ColumnName) VALUES (@collectionId, @columnName); 

SELECT SCOPE_IDENTITY();
END
GO
PRINT N'Creating [dbo].[sp_DeletePackage]'
GO
CREATE PROCEDURE [dbo].[sp_DeletePackage] 
    @templateId int,
	@packageName nvarchar(256)
AS 
BEGIN

DELETE FROM Packages WHERE TemplateId = @templateId AND PackageName = @packageName

END
GO
PRINT N'Creating [dbo].[InstanceMappings]'
GO
CREATE TABLE [dbo].[InstanceMappings]
(
[ComponentId] [int] NOT NULL,
[SourceColumnName] [nvarchar] (256) NOT NULL,
[SourceColumnUsage] [bit] NULL,
[DestinationColumnName] [nvarchar] (256) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_InstanceMappings] on [dbo].[InstanceMappings]'
GO
ALTER TABLE [dbo].[InstanceMappings] ADD CONSTRAINT [PK_InstanceMappings] PRIMARY KEY CLUSTERED  ([ComponentId], [SourceColumnName]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_UpdateInstanceMapping]'
GO
CREATE PROCEDURE [dbo].[sp_UpdateInstanceMapping] 
	@componentId int,
	@sourceColumnName nvarchar(256),
	@sourceColumnUsage	bit,
	@destinationColumnName nvarchar(256)
AS 
BEGIN
UPDATE InstanceMappings SET SourceColumnUsage = @sourceColumnUsage, DestinationColumnName = @destinationColumnName WHERE ComponentId = @componentId AND SourceColumnName = @sourceColumnName; 
END
GO
PRINT N'Creating [dbo].[PackageTemplates]'
GO
CREATE TABLE [dbo].[PackageTemplates]
(
[TemplateId] [int] NOT NULL IDENTITY(1, 1),
[TemplateName] [nvarchar] (256) NOT NULL,
[TemplateBody] [xml] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_PackageTemplates] on [dbo].[PackageTemplates]'
GO
ALTER TABLE [dbo].[PackageTemplates] ADD CONSTRAINT [PK_PackageTemplates] PRIMARY KEY CLUSTERED  ([TemplateId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_DeleteTemplate]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteTemplate] 
    @templateId int
AS 
BEGIN

DELETE FROM PackageTemplates WHERE TemplateId = @templateId;

END
GO
PRINT N'Creating [dbo].[sp_CreateInstanceColumnCollection]'
GO
CREATE PROCEDURE [dbo].[sp_CreateInstanceColumnCollection] 
	@componentId int,
	@collectionType nvarchar(128),
	@collectionName nvarchar(256)
AS 
BEGIN
INSERT INTO InstanceColumnCollections(ComponentId,CollectionType,CollectionName) VALUES (@componentId, @collectionType,@collectionName ); 

SELECT SCOPE_IDENTITY();
END
GO
PRINT N'Creating [dbo].[sp_CreateInstanceMapping]'
GO
CREATE PROCEDURE [dbo].[sp_CreateInstanceMapping] 
	@componentId int,
	@sourceColumnName nvarchar(256),
	@sourceColumnUsage	bit,
	@destinationColumnName nvarchar(256)
AS 
BEGIN
INSERT INTO InstanceMappings(ComponentId,SourceColumnName,SourceColumnUsage,DestinationColumnName) VALUES (@componentId, @sourceColumnName, @sourceColumnUsage, @destinationColumnName); 
END
GO
PRINT N'Creating [dbo].[sp_CreateTemplateInstanceComponent]'
GO
CREATE PROCEDURE [dbo].[sp_CreateTemplateInstanceComponent] 
    @componentClassId		nvarchar(128), 
	@componentName			nvarchar(256), 
	@templateInstanceId		int, 
	@componentUri			nvarchar(2048),
	@componentType			nvarchar(64)
AS 
BEGIN
	INSERT INTO TemplateInstanceComponents
	(ComponentClassId, ComponentName, TemplateInstanceId, ComponentUri,
	ComponentType)
	VALUES (
		@componentClassId, 
		@componentName, 
		@templateInstanceId, 
		@componentUri,
		@componentType);

	SELECT SCOPE_IDENTITY();
END
GO
PRINT N'Creating [dbo].[sp_CreateInstanceScalarProperty]'
GO
CREATE PROCEDURE [dbo].[sp_CreateInstanceScalarProperty] 
    @componentId int, 
	@propertyName nvarchar(256),
	@propertyType nvarchar(128),
	@propertyValue nvarchar(4000)
AS 
BEGIN
INSERT INTO InstanceScalarProperties(ComponentId,PropertyName,PropertyType,PropertyValue) VALUES (@componentId,@propertyName,@propertyType,@propertyValue); 
END
GO
PRINT N'Creating [dbo].[sp_UpdateInstanceColumnProperty]'
GO
CREATE PROCEDURE [dbo].[sp_UpdateInstanceColumnProperty] 
	@columnId		int,
	@propertyName	nvarchar(256),
	@propertyValue	nvarchar(4000)
AS 
BEGIN
	UPDATE InstanceColumnProperties	SET PropertyValue = @propertyValue WHERE ColumnId = @columnId AND PropertyName = @propertyName;
END
GO
PRINT N'Creating [dbo].[sp_CreateInstanceColumnProperty]'
GO
CREATE PROCEDURE [dbo].[sp_CreateInstanceColumnProperty] 
    @columnId int, 
	@propertyName nvarchar(256),
	@propertyValue nvarchar(4000)
AS 
BEGIN
INSERT INTO InstanceColumnProperties(ColumnId,PropertyName,PropertyValue) VALUES (@columnId, @propertyName, @propertyValue); 

END
GO
PRINT N'Creating [dbo].[sp_UpdateInstanceScalarProperty]'
GO
CREATE PROCEDURE [dbo].[sp_UpdateInstanceScalarProperty] 
	@componentId	int,
	@propertyName	nvarchar(256),
	@propertyValue	nvarchar(4000)
AS 
BEGIN
	UPDATE InstanceScalarProperties	SET PropertyValue = @propertyValue WHERE ComponentId = @componentId AND PropertyName = @propertyName;
END
GO
PRINT N'Creating [dbo].[sp_DeleteColumnCollectionProperty]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteColumnCollectionProperty] 
    @collectionId	int,
	@propertyName	nvarchar(256)
AS 
BEGIN

DELETE FROM InstanceColumnCollectionProperties WHERE CollectionId = @collectionId AND PropertyName = @propertyName;

END
GO
PRINT N'Creating [dbo].[sp_DeleteInstanceMapping]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteInstanceMapping] 
	@componentId		int,
	@sourceColumnName	nvarchar(256)
AS 
BEGIN

DELETE FROM InstanceMappings WHERE ComponentId = @componentId AND SourceColumnName = @sourceColumnName;

END
GO
PRINT N'Creating [dbo].[sp_DeleteInstanceColumnCollection]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteInstanceColumnCollection] 
    @collectionId int
AS 
BEGIN

DELETE FROM InstanceColumnCollections WHERE CollectionId = @collectionId;

END
GO
PRINT N'Creating [dbo].[sp_UpdatePackage]'
GO
CREATE PROCEDURE [dbo].[sp_UpdatePackage] 
    @templateId int, 
	@packageName nvarchar(256), 
	@packageVersion varchar(64),
	@packageBody xml,
	@changesFromTemplate xml
AS 
BEGIN

UPDATE Packages SET PackageVersion = @packageVersion, PackageBody = @packageBody, ChangesFromTemplate = @changesFromTemplate WHERE TemplateId = @templateId AND PackageName = @packageName;

END
GO
PRINT N'Creating [dbo].[sp_CreateColumnCollectionProperty]'
GO
CREATE PROCEDURE [dbo].[sp_CreateColumnCollectionProperty] 
    @collectionId int, 
	@propertyName nvarchar(256),
	@propertyValue nvarchar(4000)
AS 
BEGIN
INSERT INTO InstanceColumnCollectionProperties(CollectionId,PropertyName,PropertyValue) VALUES (@collectionId, @propertyName, @propertyValue); 
END
GO
PRINT N'Creating [dbo].[sp_DeleteTemplateInstance]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteTemplateInstance] 
    @templateInstanceID int
AS 
BEGIN

DELETE FROM TemplateInstances WHERE InstanceId = @templateInstanceID;

END
GO
PRINT N'Creating [dbo].[sp_DeleteTemplateScalarProperty]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteTemplateScalarProperty] 
    @componentId int,
	@propertyName nvarchar(256)
AS 
BEGIN

-- Delete the template scalar proeperty.
DELETE FROM TemplateScalarProperties WHERE ComponentId = @componentId AND PropertyName = @propertyName;

END
GO
PRINT N'Creating [dbo].[sp_DeleteInstanceColumn]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteInstanceColumn] 
    @columnId	int
AS 
BEGIN

DELETE FROM InstanceColumns WHERE ColumnId = @columnId;

END
GO
PRINT N'Creating [dbo].[sp_CreateTemplate]'
GO
CREATE PROCEDURE [dbo].[sp_CreateTemplate] 
    @templateName varchar(256), 
	@templateBody xml 
AS 
BEGIN
INSERT INTO PackageTemplates(TemplateName,TemplateBody) VALUES(@templateName,@templateBody);
SELECT SCOPE_IDENTITY();
END
GO
PRINT N'Creating [dbo].[sp_UpdateColumnCollectionProperty]'
GO
CREATE PROCEDURE [dbo].[sp_UpdateColumnCollectionProperty] 
	@collectionId	int,
	@propertyName	nvarchar(256),
	@propertyValue	nvarchar(4000)
AS 
BEGIN
	UPDATE InstanceColumnCollectionProperties	SET PropertyValue = @propertyValue WHERE CollectionId = @collectionId AND PropertyName = @propertyName;
END
GO
PRINT N'Creating [dbo].[sp_CreateTemplateInstance]'
GO
CREATE PROCEDURE [dbo].[sp_CreateTemplateInstance] 
    @templateName nvarchar(256), 
	@instanceName nvarchar(256) 
AS 
BEGIN
INSERT INTO TemplateInstances(TemplateId,TemplateInstanceName) 
SELECT [TemplateId],@instanceName FROM PackageTemplates WHERE [TemplateName] = @templateName;
SELECT SCOPE_IDENTITY();
END
GO
PRINT N'Creating [dbo].[TemplateComponents]'
GO
CREATE TABLE [dbo].[TemplateComponents]
(
[ComponentId] [int] NOT NULL IDENTITY(1, 1),
[ComponentClassId] [nvarchar] (128) NOT NULL,
[ComponentName] [nvarchar] (256) NOT NULL,
[TemplateId] [int] NOT NULL,
[ComponentUri] [nvarchar] (2048) NOT NULL,
[ComponentType] [nvarchar] (64) NOT NULL,
[RowsetDescriptorType] [nvarchar] (128) NULL,
[ContainsMappings] [bit] NOT NULL CONSTRAINT [DF_TemplateComponents_ContainsMappings] DEFAULT ((0)),
[ContainsCollections] [bit] NOT NULL CONSTRAINT [DF_TemplateComponents_ContainsCollections] DEFAULT ((0))
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_TemplateComponents] on [dbo].[TemplateComponents]'
GO
ALTER TABLE [dbo].[TemplateComponents] ADD CONSTRAINT [PK_TemplateComponents] PRIMARY KEY CLUSTERED  ([ComponentId]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[sp_CreateTemplateComponent]'
GO
CREATE PROCEDURE [dbo].[sp_CreateTemplateComponent] 
    @componentClassId		nvarchar(128), 
	@componentName			nvarchar(256), 
	@templateId				int, 
	@componentUri			nvarchar(2048),
	@componentType			nvarchar(64),
	@rowsetDescriptorType	nvarchar(128) = 'None',
	@containsMappings		bit,
	@containsCollections	bit
AS 
BEGIN
	INSERT INTO TemplateComponents
	(ComponentClassId, ComponentName, TemplateId, ComponentUri,
	ComponentType, RowsetDescriptorType, ContainsMappings, ContainsCollections)
	VALUES (
		@componentClassId, 
		@componentName, 
		@templateId, 
		@componentUri,
		@componentType,
		@rowsetDescriptorType,
		@containsMappings,
		@containsCollections);

	SELECT SCOPE_IDENTITY();
END
GO
PRINT N'Creating [dbo].[sp_UpdateTemplateComponent]'
GO
CREATE PROCEDURE [dbo].[sp_UpdateTemplateComponent] 
	@componentId			int,
	@containsMappings		bit,
	@containsCollections	bit
AS 
BEGIN
	UPDATE TemplateComponents
	SET ContainsMappings = @containsMappings, ContainsCollections = @containsCollections WHERE ComponentId = @componentId;
END
GO
PRINT N'Creating [dbo].[sp_DeleteTemplateComponent]'
GO
CREATE PROCEDURE [dbo].[sp_DeleteTemplateComponent] 
    @componentId int
AS 
BEGIN

-- Delete the component.
DELETE FROM TemplateComponents WHERE ComponentId = @componentId;

END
GO
PRINT N'Adding constraints to [dbo].[InstanceColumnCollectionProperties]'
GO
ALTER TABLE [dbo].[InstanceColumnCollectionProperties] ADD CONSTRAINT [UQ__InstanceColumnCo__08EA5793] UNIQUE NONCLUSTERED  ([CollectionId], [PropertyName]) ON [PRIMARY]
GO
PRINT N'Adding constraints to [dbo].[InstanceColumnCollections]'
GO
ALTER TABLE [dbo].[InstanceColumnCollections] ADD CONSTRAINT [IX_InstanceColumnCollections] UNIQUE NONCLUSTERED  ([ComponentId], [CollectionType], [CollectionName]) ON [PRIMARY]
GO
PRINT N'Adding constraints to [dbo].[InstanceColumnProperties]'
GO
ALTER TABLE [dbo].[InstanceColumnProperties] ADD CONSTRAINT [UQ__InstanceColumnPr__03317E3D] UNIQUE NONCLUSTERED  ([ColumnId], [PropertyName]) ON [PRIMARY]
GO
PRINT N'Adding constraints to [dbo].[InstanceColumns]'
GO
ALTER TABLE [dbo].[InstanceColumns] ADD CONSTRAINT [UQ__InstanceColumns__25518C17] UNIQUE NONCLUSTERED  ([CollectionId], [ColumnName]) ON [PRIMARY]
GO
PRINT N'Adding constraints to [dbo].[PackageTemplates]'
GO
ALTER TABLE [dbo].[PackageTemplates] ADD CONSTRAINT [UQ__PackageTemplates__117F9D94] UNIQUE NONCLUSTERED  ([TemplateName]) ON [PRIMARY]
GO
PRINT N'Adding constraints to [dbo].[TemplateInstances]'
GO
ALTER TABLE [dbo].[TemplateInstances] ADD CONSTRAINT [UQ__TemplateInstance__33D4B598] UNIQUE NONCLUSTERED  ([TemplateId], [TemplateInstanceName]) ON [PRIMARY]
GO
PRINT N'Adding constraints to [dbo].[TemplateScalarProperties]'
GO
ALTER TABLE [dbo].[TemplateScalarProperties] ADD CONSTRAINT [IX_TemplateScalarProperties] UNIQUE NONCLUSTERED  ([ComponentId], [PropertyName]) ON [PRIMARY]
GO
PRINT N'Adding foreign keys to [dbo].[InstanceColumnCollectionProperties]'
GO
ALTER TABLE [dbo].[InstanceColumnCollectionProperties] ADD
CONSTRAINT [FK_InstanceColumnCollectionProperties_InstanceColumnCollections] FOREIGN KEY ([CollectionId]) REFERENCES [dbo].[InstanceColumnCollections] ([CollectionId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[TemplateScalarProperties]'
GO
ALTER TABLE [dbo].[TemplateScalarProperties] ADD
CONSTRAINT [FK_TemplateScalarProperties_TemplateComponents] FOREIGN KEY ([ComponentId]) REFERENCES [dbo].[TemplateComponents] ([ComponentId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[TemplateInstances]'
GO
ALTER TABLE [dbo].[TemplateInstances] ADD
CONSTRAINT [FK__TemplateI__Templ__1FCDBCEB] FOREIGN KEY ([TemplateId]) REFERENCES [dbo].[PackageTemplates] ([TemplateId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[TemplateComponents]'
GO
ALTER TABLE [dbo].[TemplateComponents] ADD
CONSTRAINT [FK_TemplateComponents_PackageTemplates] FOREIGN KEY ([TemplateId]) REFERENCES [dbo].[PackageTemplates] ([TemplateId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[Packages]'
GO
ALTER TABLE [dbo].[Packages] ADD
CONSTRAINT [FK_Packages_PackageTemplates] FOREIGN KEY ([TemplateId]) REFERENCES [dbo].[PackageTemplates] ([TemplateId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[InstanceMappings]'
GO
ALTER TABLE [dbo].[InstanceMappings] ADD
CONSTRAINT [FK_InstanceMappings_TemplateInstanceComponents] FOREIGN KEY ([ComponentId]) REFERENCES [dbo].[TemplateInstanceComponents] ([ComponentId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[InstanceColumnCollections]'
GO
ALTER TABLE [dbo].[InstanceColumnCollections] ADD
CONSTRAINT [FK_InstanceColumnCollections_TemplateInstanceComponents] FOREIGN KEY ([ComponentId]) REFERENCES [dbo].[TemplateInstanceComponents] ([ComponentId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[InstanceColumnProperties]'
GO
ALTER TABLE [dbo].[InstanceColumnProperties] ADD
CONSTRAINT [FK_InstanceColumnProperties_InstanceColumns] FOREIGN KEY ([ColumnId]) REFERENCES [dbo].[InstanceColumns] ([ColumnId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[InstanceScalarProperties]'
GO
ALTER TABLE [dbo].[InstanceScalarProperties] ADD
CONSTRAINT [FK_InstanceScalarProperties_TemplateInstanceComponents] FOREIGN KEY ([ComponentId]) REFERENCES [dbo].[TemplateInstanceComponents] ([ComponentId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[InstanceColumns]'
GO
ALTER TABLE [dbo].[InstanceColumns] ADD
CONSTRAINT [FK_InstanceColumns_InstanceColumnCollections] FOREIGN KEY ([CollectionId]) REFERENCES [dbo].[InstanceColumnCollections] ([CollectionId]) ON DELETE CASCADE
GO
PRINT N'Adding foreign keys to [dbo].[TemplateInstanceComponents]'
GO
ALTER TABLE [dbo].[TemplateInstanceComponents] ADD
CONSTRAINT [FK_TemplateInstanceComponents_TemplateInstances] FOREIGN KEY ([TemplateInstanceId]) REFERENCES [dbo].[TemplateInstances] ([InstanceId]) ON DELETE CASCADE
GO

GO
/*
 Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script		
 Use SQLCMD syntax to include a file into the post-deployment script			
 Example:      :r .\filename.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/









